﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.HSSF.Util;
using System.IO;

namespace portero_
{
    public class clsNPOI
    {

        public void generaXls(string ruta, string titulo, DataGridView dgv)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

            dsi.Company = "MaxZ Soft";
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "Generado por MaxZ Soft";
            workbook.SummaryInformation = si;

            int numHoja = 1;
            HSSFSheet sheet = workbook.CreateSheet("Hoja" + numHoja.ToString());
            setHeader(sheet, dgv, workbook, titulo);

            int regXHoja = 1;
            int regTotal = 0;

            foreach (DataGridViewRow reg in dgv.Rows)
            {
                regTotal++;
                regXHoja++;
                int contCell = 0;
                foreach (DataGridViewCell cell in reg.Cells)
                {
                    if (cell.Value != null)
                    {
                        sheet.CreateRow(regXHoja).CreateCell(contCell).SetCellValue(cell.Value.ToString());
                    }
                    else
                    {
                        sheet.CreateRow(regXHoja).CreateCell(contCell).SetCellValue("");
                    }
                    contCell++;
                }
                if (regXHoja > 65000)
                {
                    numHoja++;
                    sheet = workbook.CreateSheet("Hoja" + numHoja.ToString());
                    setHeader(sheet, dgv, workbook, titulo);
                    regXHoja = 1;
                }
            }
            FileStream file = new FileStream(ruta, FileMode.Create);
            workbook.Write(file);
            file.Close();

        }
        private void setHeader(HSSFSheet sheet,DataGridView dgv,HSSFWorkbook hssfworkbook,String titulo)
        {
            int contCampos = dgv.Columns.Count;
            HSSFCellStyle styleTit = hssfworkbook.CreateCellStyle();

            styleTit.FillForegroundColor = HSSFColor.YELLOW.index;
            styleTit.FillBackgroundColor = HSSFColor.ROSE.index;
            styleTit.Alignment = HSSFCellStyle.ALIGN_CENTER;

            HSSFFont font1 = hssfworkbook.CreateFont();
            font1.Color = HSSFColor.RED.index;
            font1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
            font1.FontHeightInPoints = 10;

            styleTit.SetFont(font1);
            sheet.CreateRow(0).CreateCell(0).SetCellValue(titulo);
            sheet.GetRow(0).GetCell(0).CellStyle = styleTit;
            sheet.AddMergedRegion(new Region(0, 0, 0, contCampos - 1));
            for (int ix = 0; ix < dgv.Columns.Count; ix++)
            {
                sheet.SetColumnWidth(ix, dgv.Columns[ix].Width*40);
            }

            int ixCampo = 0;
            while (ixCampo  < contCampos)
            {
                String nomColumn = dgv.Columns[ixCampo].HeaderText;
                sheet.CreateRow(1).CreateCell(ixCampo).SetCellValue(nomColumn);
                sheet.GetRow(1).GetCell(ixCampo).CellStyle = styleTit;
                ixCampo++;
            }
        }
    }
}
